from utils import parse_util
from database.mysqlhelper import MySqLHelper

db = MySqLHelper()

insert_sql = 'insert into t_boards_info (f_code,f_name,f_total_val,f_platform,f_board_code,f_board_one,f_board_two,f_board_thr) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) '
select_sql = 'select f_code,f_name,f_total_val,f_platform,f_board_code,f_board_one,' \
             'f_board_two,f_board_thr,f_stock_info,f_update_time from t_boards_info '


# 插入多条
def insert_mul(data_list):
    # 增加多条
    li = []
    for info in data_list:
        li.append((info['code'], info['name'], info['total_val'], info['platform'], info['board_code'],
                   info['board_one'], info['board_two'], info['board_thr']))
    return db.insertmany(insert_sql, li)


# 所有板块
def selectall():
    return format_res(db.selectall(select_sql))


# 所有板块
def select_all_board_code():
    board_list = list(db.selectall('select distinct f_board_code from t_boards_info '))
    for i in range(len(board_list)):
        board_list[i] = str(board_list[i][0], 'utf-8')
    return board_list


# 所有板块名称
def select_all_board_name():
    board_name_list = list(db.selectall('select distinct f_board_one from t_boards_info '))
    for i in range(len(board_name_list)):
        board_name_list[i] = str(board_name_list[i][0], 'utf-8')
    return board_name_list


def select_board_by_stocks(codes: list):
    sql = 'select distinct f_board_code from db_stock.t_boards_info where f_code in ( '
    for code in codes:
        sql += '\'' + code + '\','
    res = db.selectall(sql[:-1] + ')')
    list = []
    for r in res:
        list.append(str(r[0], encoding="utf-8"))
    return list


# 获取编码
def select_stock_code(stock_code):
    sql = select_sql + ' where f_code = %s '
    return format_res(db.selectall(sql, (stock_code)))


# 获取一条
def select_board_one(board_code):
    sql = select_sql + ' where f_board_code = %s limit 1 '
    return format_one(db.selectone(sql, (board_code)))


# 获取板块明细
def select_board_detail(board_code):
    return format_res(db.selectall(select_sql + ' where f_board_code = %s ', (board_code)))


# 获取板块明细前几
def select_board_detail_val_top_n(board_code, num):
    return format_res(
        db.selectall(select_sql + ' where f_board_code = %s order by f_total_val desc limit %s', (board_code, num)))


# 删除一版
def delete_board(board_code):
    sql = 'delete from t_boards_info where f_board_code = %s '
    return db.delete(sql, (board_code))


# 更新市值
def update_total_val(code, total_val):
    sql1 = 'update t_boards_info set f_total_val = %s where f_code = %s  '
    return db.update(sql1, (total_val, code))


# 更新股票信息
def update_stock_info(code, info):
    sql1 = 'update t_boards_info set f_stock_info = %s where f_code = %s  '
    return db.update(sql1, (info, code))


# 格式化结果
def format_one(res):
    if not res:
        return {}
    return {'code': str(res[0], encoding="utf-8"), 'name': str(res[1], encoding="utf-8"),
            'total_val': int(res[2]), 'platform': str(res[3], encoding="utf-8"),
            'board_code': str(res[4], encoding="utf-8"), 'board_one': str(res[5], encoding="utf-8"),
            'board_two': str(res[6], encoding="utf-8"), 'board_thr': str(res[7], encoding="utf-8"),
            'stock_info': parse_util.byte_to_str(res[8]),
            'up_time': res[9]}


# f_code,f_name,f_total_val,f_platform,f_board_code,f_board_one,f_board_two,f_board_thr,f_update_time
# 格式化结果
def format_res(res_list):
    list = []
    for res in res_list:
        list.append(format_one(res))
    return list


if __name__ == '__main__':
    select_board_by_stocks(['60031', '600316'])
